<?php

namespace app\admin\service;

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;
use PhpOffice\PhpSpreadsheet\Cell\DataType;
use PhpOffice\PhpSpreadsheet\Cell\DataValidation;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

use app\common\BaseHelper;

use app\admin\controller\Excel;

use app\admin\model\Member as MemberMdl;
use app\admin\model\Povertymember;
use app\admin\model\Memberextrainfo;
use app\admin\model\Memberresume;
use app\admin\model\Organization;
use app\admin\model\Ethnic;
use app\admin\model\Villages;
use app\admin\model\Area;

use think\Db;
use think\Exception;

class Member
{

    /**
     * 保存额外属性信息
     */
    public static function saveExtrainfo($params){
        if(!$params){
            return ['code' => 0, 'msg' => '参数错误'];
        }

        $memberId = $params['id'];
        $model = new Memberextrainfo();
        $info = $model->where(['memberId' => $memberId])->field('extraId, memberId')->find();
        try {
            Db::startTrans();

            $data = [
                'memberId' => $memberId,
//                'isHouseholder' => isset($params['isHouseholder']) ? $params['isHouseholder'] : '',
//                'householderId' => isset($params['householderId']) ? $params['householderId'] : '',
//                'relatedHouseholder' => isset($params['relatedHouseholder']) ? $params['relatedHouseholder'] : '',
//                'laborSkills' => isset($params['laborSkills']) ? $params['laborSkills'] : '',
//                'perIncome' => isset($params['perIncome']) ? $params['perIncome'] * 100 : '',
//                'workStatus' => isset($params['workStatus']) ? $params['workStatus'] : '',
//                'workTime' => isset($params['workTime']) ? $params['workTime'] : '',
//                'specialGroup' => isset($params['specialGroup']) ? $params['specialGroup'] : '',
//                'workDuty' => isset($params['workDuty']) ? $params['workDuty'] : '',
            ];
            isset($params['isHouseholder']) && $data['isHouseholder'] = $params['isHouseholder'];
            isset($params['householderId']) && $data['householderId'] = $params['householderId'];
            isset($params['relatedHouseholder']) && $data['relatedHouseholder'] = $params['relatedHouseholder'];
            isset($params['laborSkills']) && $data['laborSkills'] = $params['laborSkills'];
            isset($params['perIncome']) && $data['perIncome'] = $params['perIncome'];
            isset($params['workStatus']) && $data['workStatus'] = $params['workStatus'];
            isset($params['workTime']) && $data['workTime'] = $params['workTime'];
            isset($params['specialGroup']) && $data['specialGroup'] = $params['specialGroup'];
            isset($params['workDuty']) && $data['workDuty'] = $params['workDuty'];
            if ($info) {
                // 更新 / 将已删除的更新
                $data['extraId'] = $info['extraId'];
                if(isset($params['householdNum'])){
                    if($params['householdNum'] == '' || $params['personNum'] == ''){
                        // 是否是户主
                        if($params['isHouseholder'] == 1){
                            // 户编号
                            $maxHouseholdNum = $model->max('householdNum');
                            if($maxHouseholdNum){
                                $householdNum = $maxHouseholdNum + 1;
                            }else{
                                $householdNum = '1000000001';
                            }
                            // 人编号
                            $personNum = $householdNum . '001';
                        }else{
                            // 户编号
                            $household = $model->where(['memberId' => $params['householderId']])->field('householdNum, personNum')->find();
                            $householdNum = $household['householdNum'];
                            // 人编号
                            $personNum = $household['householdNum'] + 1;
                        }
                        $data['householdNum'] = $householdNum;
                        $data['personNum'] = $personNum;
                    }
                }

                $result = $model->allowField(true)->save($data, ['extraId' => $data['extraId']]);
            } else {
                // 新增
                $data['extraId'] = BaseHelper::getUUID();
                if(isset($params['isHouseholder'])){
                    // 是否是户主
                    if($params['isHouseholder'] == 1){
                        // 户编号
                        $maxHouseholdNum = $model->max('householdNum');
                        if($maxHouseholdNum){
                            $householdNum = $maxHouseholdNum + 1;
                        }else{
                            $householdNum = '1000000001';
                        }
                        // 人编号
                        $personNum = $householdNum . '001';
                    }else{
                        // 户编号
                        $household = $model->where(['memberId' => $params['householderId']])->field('householdNum, personNum')->find();
                        $householdNum = $household['householdNum'];
                        // 人编号
                        $personNum = $household['householdNum'] + 1;
                    }
                    $data['householdNum'] = $householdNum;
                    $data['personNum'] = $personNum;
                }

                $result = $model->save($data);
            }

            if($result !== false) {
                // 保存工作履历
                $memberresumeMdl = new Memberresume();
                // 当只保存村民信息时，没有提交履历，不能进行删除操作
                if(!isset($params['onlyVillager'])){
                    // 删除之前的履历
                    $memberresumeMdl->where(['memberId' => $memberId])->delete();
                }
                $startTime = isset($params['startTime']) ? (Array)$params['startTime'] : [];
                $endTime = isset($params['endTime']) ? (Array)$params['endTime'] : [];
                $resume = isset($params['resume']) ? (Array)$params['resume'] : '';
                if($resume){
                    // 删除之前的履历
                    //$memberresumeMdl->where(['memberId' => $memberId])->delete();
                    foreach ($resume as $key => $value) {
                        $resumeData = [
                            'id' => BaseHelper::getUUID(),
                            'memberId' => $memberId,
                            'startTime' => strtotime($startTime[$key]),
                            'endTime' => strtotime($endTime[$key]),
                            'resume' => $value,
                            'sorting' => $key
                        ];
                        // 新增履历
                        $res = $memberresumeMdl->insert($resumeData);
                        if($res === false){
                            Db::rollback();
                            return ['code' => 0, 'msg' => $model->getError()];
                        }
                    }
                }

                Db::commit();
                return ['code' => 1, 'msg' => '保存成功'];
            } else {
                Db::rollback();
                return ['code' => 0, 'msg' => $model->getError()];
            }
        } catch (Exception $e) {
            Db::rollback();
            return ['code' => 0, 'msg' => $e->getMessage()];
        }
    }

    /**
     * 下载Excel模板
     */
    public static function downloadTemplate($request){
        $param = $request->param();
        $townId = $param['townId'];

        $xlsTitle = 'Templete';
        $spreadsheet = new Spreadsheet();
        $spreadsheet->setActiveSheetIndex(0);
        $spreadsheet->getActiveSheet()->setTitle($xlsTitle);

        $spreadsheet->getActiveSheet()->setCellValue('A1', '村民基本信息(注意：导入数据时请只保留此模板的第二行作为表头（即为导入Excel的第一行），然后，从第二行开始添加有效数据。)');
        // 合并列
        $spreadsheet->getActiveSheet()->mergeCells('A1:Z1');
        $spreadsheet->getActiveSheet()->getColumnDimension('A')->setWidth(20);
        $spreadsheet->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);

        $index0 = 2;
        $cellArray0 = array(
            'A' => '所属镇(不填则为外来人口)',
            'B' => '所属村(可选填)',
            'C' => '姓名',
            'D' => '性别(1=男|2=女)',
            'E' => '出生日期(可选填，格式：2018-08-08)',
            'F' => '身份证号',
            'G' => '手机号(可选填)',
            'H' => '是否是户主(1=是|2=否)',
            'I' => '户主',
            'J' => '与户主关系',
            'K' => '民族',
            'L' => '文化程度',
            'M' => '健康状况',
            'N' => '宗教信仰',
            'O' => '劳动技能',
            'P' => '人均收入(单位：元)',
            'Q' => '务工状况',
            'R' => '务工时间(单位：月)',
            'S' => '特殊群体(0=否|1=留守儿童|2=空巢老人)',
            'T' => '详细住址',
            'U' => '村职务',
            'V' => '是否为党员(1=是|2=否)',
            'W' => '组织(党员必填项)',
            'X' => '职务名称(党员必填项)',
            'Y' => '入党时间(党员必填项，格式：2018-08-08)',
            'Z' => '工作职责',
        );
        foreach ($cellArray0 as $key0 => $cell0) {
            $spreadsheet->getActiveSheet()->setCellValue($key0 . $index0, $cell0);
            //$spreadsheet->getActiveSheet()->getColumnDimension($key0)->setWidth(10);
            //$spreadsheet->getActiveSheet()->getColumnDimension($key0)->setAutoSize(true);
        }
        /*
        $list = 'select1,select2,select3';
        $objValidation = $spreadsheet->getActiveSheet()->getCell('B2')->getDataValidation(); // 从第二行开始有下拉样式
        $objValidation->setType(DataValidation::TYPE_LIST)
            ->setErrorStyle(DataValidation::STYLE_INFORMATION)
            ->setAllowBlank(false)
            ->setShowInputMessage(true)
            ->setShowErrorMessage(true)
            ->setShowDropDown(true)
            ->setErrorTitle('输入的值有误')
            ->setError('您输入的值不在下拉框列表内.')
            ->setPromptTitle('设备类型')
            ->setPrompt('')
            //->setFormula1('mySheet2!$A$1:$A$3'); //把sheet名为mySheet2的 A1,A2,A3 作为选项
            ->setFormula1('"' . $list . '"'); // 左右引号不要去掉，去掉会出错
        */

        $cellArray = ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', 'AA', 'AB'];
        // 与户主关系
        $spreadsheet->getActiveSheet()->setCellValue('A4', '与户主关系');
        $spreadsheet->getActiveSheet()->getStyle('A4')->getFont()->setBold(true);
        $index0 = 5;
        $relatedHouseholders = get_relatedHouseholder();
        $i0 = 0;
        foreach ($relatedHouseholders as $key0 => $cell0) {
            $spreadsheet->getActiveSheet()->setCellValue($cellArray[$i0] . $index0, $cell0);
            $i0 ++;
        }
        //$spreadsheet->getActiveSheet()->setCellValue('A4', '民族');
        // 文化程度
        $spreadsheet->getActiveSheet()->setCellValue('A7', '文化程度');
        $spreadsheet->getActiveSheet()->getStyle('A7')->getFont()->setBold(true);
        $index1 = 8;
        $educationList = get_education();
        $i1 = 0;
        foreach ($educationList as $key1 => $cell1) {
            $spreadsheet->getActiveSheet()->setCellValue($cellArray[$i1] . $index1, $cell1);
            $i1 ++;
        }

        // 健康状况
        $spreadsheet->getActiveSheet()->setCellValue('A10', '健康状况');
        $spreadsheet->getActiveSheet()->getStyle('A10')->getFont()->setBold(true);
        $index2 = 11;
        $healthList = get_health();
        $i2 = 0;
        foreach ($healthList as $key2 => $cell2) {
            $spreadsheet->getActiveSheet()->setCellValue($cellArray[$i2] . $index2, $cell2);
            $i2 ++;
        }

        // 宗教信仰
        $spreadsheet->getActiveSheet()->setCellValue('A13', '宗教信仰');
        $spreadsheet->getActiveSheet()->getStyle('A13')->getFont()->setBold(true);
        $index3 = 14;
        $religiousFaithList = get_religiousFaith();
        $i3 = 0;
        foreach ($religiousFaithList as $key3 => $cell3) {
            $spreadsheet->getActiveSheet()->setCellValue($cellArray[$i3] . $index3, $cell3);
            $i3 ++;
        }

        // 劳动技能
        $spreadsheet->getActiveSheet()->setCellValue('A16', '劳动技能');
        $spreadsheet->getActiveSheet()->getStyle('A16')->getFont()->setBold(true);
        $index4 = 17;
        $laborSkillsList = get_laborSkills();
        $i4 = 0;
        foreach ($laborSkillsList as $key4 => $cell4) {
            $spreadsheet->getActiveSheet()->setCellValue($cellArray[$i4] . $index4, $cell4);
            $i4 ++;
        }

        // 务工状况
        $spreadsheet->getActiveSheet()->setCellValue('A19', '务工状况');
        $spreadsheet->getActiveSheet()->getStyle('A19')->getFont()->setBold(true);
        $index5 = 20;
        $workStatusList = get_workStatus();
        $i5 = 0;
        foreach ($workStatusList as $key5 => $cell5) {
            $spreadsheet->getActiveSheet()->setCellValue($cellArray[$i5] . $index5, $cell5);
            $i5 ++;
        }

        $town = db('area')->where(['id' => $townId])->value('name');
        // 镇下的村子
        $spreadsheet->getActiveSheet()->setCellValue('A22', $town . '下的村子');
        $spreadsheet->getActiveSheet()->getStyle('A22')->getFont()->setBold(true);
        $index20 = 23;
        $villageList = Villages::where(['townId' => $townId])->field('name')->select();
        foreach ($villageList as $key20 => $cell20) {
            $spreadsheet->getActiveSheet()->setCellValue('A' . ($index20), $cell20['name']);
            $index20 ++;
        }

        // 镇下的组织
        $index30 = 23 + count($villageList) + 2;
        $spreadsheet->getActiveSheet()->setCellValue('A' . ($index30 -1), $town . '下的组织');
        $spreadsheet->getActiveSheet()->getStyle('A' . ($index30 -1))->getFont()->setBold(true);
        $organizationList = Organization::where(['townId' => $townId, 'isDelete' => 2])->field('name,villageId')->select();
        foreach ($organizationList as $key30 => $cell30) {
            $villageName = Villages::where(['id' => $cell30['villageId']])->value('name');
            $spreadsheet->getActiveSheet()->setCellValue('A' . ($index30), $cell30['name']);
            $spreadsheet->getActiveSheet()->setCellValue('B' . ($index30), $villageName);
            $index30 ++;
        }

        $filePath = ROOT_PATH . 'public' . DS . 'upload' . DS . 'csv' . DS . 'member' . DS . 'templete' . DS . date('Y-m-d');
        $fileName = $filePath . '/member_templete_' . date('y-m-d-H-i-s');

        //检查文件或目录是否存在
        if(!file_exists($filePath)){
            mkdir($filePath, 0777, true);
        }
        $writer = new Xlsx($spreadsheet);
        //$writer->save($fileName . '.xls'); // or .xlsx

        $xlsTitle = iconv('utf-8', 'gb2312', $xlsTitle); // 文件名称
        $downFileName = basename($fileName,".".pathinfo($fileName, PATHINFO_EXTENSION));
        header('pragma:public');
        header('Content-type:application/vnd.ms-excel;charset=utf-8;name="'.$xlsTitle.'.xls"');
        header("Content-Disposition:attachment;filename=$downFileName.xls"); // attachment 新窗口打印,inline 本窗口打印

        $writer->save('php://output');
        exit;
    }

    /**
     * 导入数据
     */
    public static function import(){
        $session_admin = session('ADMIN');
        $file = request()->file('csv_file');
        $model = new MemberMdl();

        Db::startTrans();
        try {
            $filePath = ROOT_PATH . 'public' . DS . 'upload';
            $info = $file->move($filePath);
            $fileName = $info->getSaveName();
            //$fileName = 'user_' . date('y-m-d-H-i-s') . '.' . substr($file,strripos($file,'.')+1);
            //move_uploaded_file($file, iconv("UTF-8","gb2312", $filePath . DS . $fileName));
            $fileType = \PhpOffice\PhpSpreadsheet\IOFactory::identify($filePath . DS . $fileName);
            $reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader($fileType);
            // 设置编码
            if(strtolower($fileType) == 'csv'){
                $reader->setInputEncoding('CP936'); // 只对csv文件有效
            }
            $spreadsheet = $reader->load($filePath . DS . $fileName);

            $sheetData = $spreadsheet->getActiveSheet()->toArray();
            if(count($sheetData) <= 1){
                return json(['status' => 0, 'msg' => '未找到文件内容，请填写数据。']);
            }
            foreach ($sheetData as $key => $val) {
                if($key == 0){
                    continue;
                }
                if(count($val) < 26){
                    Db::rollback();
                    return json(['status' => 0, 'msg' => '文件内容不正确，请按照模板来填写内容。']);
                }

                if ($val[0]) {
                    // 本地居民
                    $memberType = 1;
                    $townId = Area::where(['name' => $val[0]])->value('id');
                    if(!$townId){
                        Db::rollback();
                        return json(['status' => 0, 'msg' => '未找到' . $val[0]]);
                    }
                    $areaIds = action('admin/Base/getParentIdsByTownId', ['townId' => $townId]);
                    $cityId = $areaIds['cityId'];
                    $xianId = $areaIds['xianId'];
                    if($val[1]){
                        $villageId = Villages::where(['name' => $val[1]])->value('id');
                        if(!$villageId){
                            Db::rollback();
                            return json(['status' => 0, 'msg' => '未找到' . $val[1]]);
                        }
                    }else{
                        $villageId = 0;
                    }
                } else {
                    // 外来人口
                    $memberType = 2;
                    $cityId = 0;
                    $xianId = 0;
                    $townId = 0;
                    $villageId = 0;
                }

                // 必填字段 验证
                if(!$val[2] || !$val[5]){
                    Db::rollback();
                    return json(['status' => 0, 'msg' => '姓名、身份证号等是必填字段']);
                }
                // 姓名
                $name = $val[2];
                // 性别
                $gender = in_array($val[3], [1, 2]) ? $val[3] : 1;
                // 出生日期
                $birthday = date('Y-m-d', strtotime($val[4]));
                // 身份证号
                $identityNumber = $val[5];
                // 检查身份证号是否存在
                $identityNumberInfo = MemberMdl::where(['identityNumber' => $val[5], 'isDelete' => 2])->value('identityNumber');
                if($identityNumberInfo){
                    Db::rollback();
                    return json(['status' => 0, 'msg' => '身份证号 '. $val[5] .' 已存在！']);
                }
                // 手机号
                $mobile = $val[6];
                // 是否是户主
                $isHouseholder = in_array($val[7], [1, 2]) ? $val[7] : 2;
                if ($isHouseholder == 2) {
                    // 户主
                    $householder = MemberMdl::where('name', $val[8])->field('id, name')->find();
                    if(!$householder){
                        Db::rollback();
                        return json(['status' => 0, 'msg' => '未找到户主 ' . $val[8]]);
                    }
                    $householderId = $householder['id'];
                    // 与户主关系
                    $relatedHouseholderArr = get_relatedHouseholder();
                    $relatedHouseholder = 0;
                    foreach($relatedHouseholderArr as $relateKey => $relateValue){
                        if($relateValue == $val[9]){
                            $relatedHouseholder = $relateKey;
                            break;
                        }
                    }
                } else {
                    $householderId = '';
                    $relatedHouseholder = '';
                }
                // 民族
                $ethnicId = Ethnic::where(['name' => $val[10]])->value('id');
                if(!$ethnicId){
                    $ethnicId = Ethnic::where(['name' => '汉族'])->value('id');
                }
                // 文化程度
                $educationArr = get_education();
                $education = 1;
                foreach($educationArr as $eduKey => $eduValue){
                    if($eduValue == $val[11]){
                        $education = $eduKey;
                        break;
                    }
                }
                // 健康状况
                $healthArr = get_health();
                $health = 1;
                foreach($healthArr as $healthKey => $healthValue){
                    if($healthValue == $val[12]){
                        $health = $healthKey;
                        break;
                    }
                }
                // 宗教信仰
                $religiousFaithArr = get_religiousFaith();
                $religiousFaith = 1;
                foreach($religiousFaithArr as $faithKey => $faithValue){
                    if($faithValue == $val[13]){
                        $religiousFaith = $faithKey;
                        break;
                    }
                }
                // 劳动技能
                $laborSkillsArr = get_laborSkills();
                $laborSkills = 1;
                foreach($laborSkillsArr as $skillKey => $skillValue){
                    if($skillValue == $val[14]){
                        $laborSkills = $skillKey;
                        break;
                    }
                }
                // 人均收入(单位：元)
                $perIncome = number_format($val[15], 2, '.', '');
                $perIncome = $perIncome * 100; // 数据库保存 单位为分
                // 务工状况
                $workStatusArr = get_workStatus();
                $workStatus = 1;
                foreach($workStatusArr as $workKey => $workValue){
                    if($workValue == $val[16]){
                        $workStatus = $workKey;
                        break;
                    }
                }
                // 务工时间(单位：月)
                $workTime = (Int)$val[17];
                // 特殊群体(0=否|1=留守儿童|2=空巢老人)
                $specialGroup = in_array($val[18], [0, 1, 2]) ? $val[18] : 0;
                // 详细住址
                $address = $val[19];
                // 村职务
                $villageJob = $val[20];
                // 是否为党员(1=是|2=否)
                $isParty = in_array($val[21], [1, 2]) ? $val[21] : 2;
                if ($isParty == 1) {
                    // 组织
                    if($villageId != 0){
                        // 找到该村下的组织
                        $organizationList = Organization::where(['villageId' => $villageId])->field('name')->select();
                        $organizationList = array_column($organizationList, 'name');
                        if(!in_array($val[22], $organizationList)){
                            Db::rollback();
                            return json(['status' => 0, 'msg' => $val[1] . '下面没有 '. $val[22] .'组织！']);
                        }
                    }
                    $organizationId = Organization::where(['name' => $val[22]])->value('id');
                    $job = $val[23];
                    $partyTime = strtotime($val[24]);
                } else {
                    $organizationId = $job = $partyTime = '';
                }
                // 工作职责
                $workDuty = $val[25];

                $data = [
                    'id'                 => BaseHelper::getUUID(),
                    'cityId'             => $cityId,
                    'xianId'             => $xianId,
                    'townId'             => $townId,
                    'villageId'          => $villageId,
                    'memberType'         => $memberType,
                    'name'               => $name,
                    'gender'             => $gender,
                    'birthday'           => $birthday,
                    'identityNumber'     => $identityNumber,
                    'mobile'             => $mobile,
                    'ethnicId'           => $ethnicId,
                    'religiousFaith'     => $religiousFaith,
                    'education'          => $education,
                    'health'             => $health,
                    'address'            => $address,
                    'villageJob'         => $villageJob,
                    'isParty'            => $isParty,
                    'organizationId'     => $organizationId,
                    'job'                => $job,
                    'partyTime'          => $partyTime,
                    'createDate'         => time(),
                    'createOper'         => $session_admin->id,
                    'checkParty'         => 1,
                    'checkInstitution'   => 1,
                    'checkVillager'      => 1,
                    'checkPoverty'       => 1,

                    // 额外属性信息
                    'isHouseholder'      => $isHouseholder,
                    'householderId'      => $householderId,
                    'relatedHouseholder' => $relatedHouseholder,
                    'laborSkills'        => $laborSkills,
                    'perIncome'          => $perIncome,
                    'workStatus'         => $workStatus,
                    'workTime'           => $workTime,
                    'specialGroup'       => $specialGroup,
                    'workDuty'           => $workDuty,
                ];

                $memberInfo = MemberMdl::where(['identityNumber' => $identityNumber])->find();
                if($memberInfo){
                    // 将已删除的村民 进行更新
                    $data['id'] = $memberInfo['id'];
                    $data['createDate'] = time();
                    $data['createOper'] = $session_admin->id;
                    $data['isDelete'] = 2;
                    $result = $model->allowField(true)->save($data, ['id' => $data['id']]);
                }else{
                    $result = $model->isUpdate(false)->save($data);
                }

                if($result === false){
                    Db::rollback();
                    return json(['status' => 0, 'msg' => '导入失败！']);
                }

                // 保存额外属性信息
                $res = self::saveExtrainfo($data);
                if($res['code'] != 1){
                    Db::rollback();
                    //return json(['status' => 0, 'msg' => '导入额外属性信息失败！']);
                    return json(['status' => 0, 'msg' => $res['msg']]);
                }
            }
            @unlink($filePath . DS . $fileName);

            Db::commit();
            return json(['status' => 1, 'msg' => '导入成功！']);
        } catch (\Exception $e){
            Db::rollback();
            $msg = $e->getMessage();
            $msg = "导入失败，请严格按照模板来填写内容。\n" . $msg;
            return json(['status' => 0, 'msg' => $msg]);
        }

        return json(['status' => 0, 'msg' => $msg]);
    }

    /**
     * 导出数据
     */
    public static function export($request){
        $param = $request->param();

        $where = [
            'isDelete' => 2,
            //'shenheStatus' => ['<', 2]
        ];
        if(!empty($param['cityId'])){
            $where['cityId'] = $param['cityId'];
        }
        if(!empty($param['xianId'])){
            $where['xianId'] = $param['xianId'];
        }
        if(!empty($param['townId'])){
            $where['townId'] = $param['townId'];
        }
        if(!empty($param['villageId'])){
            $where['villageId'] = $param['villageId'];
        }
        if(!empty($param['name'])){
            $where['name'] = ['like', '%'.$param['name'].'%'];
        }

        $model = model('Member');
        $data = $model->where($where)->order('createDate', 'asc')->select();
        if(count($data) <= 0){
            return [
                'code' => 2,
                'msg' => '没有数据！'
            ];
        }

        $areaDB = db('area');
        $villageDB = db('villages');
        $organizationDB = db('organization');
        $institutionDB = db('institution');
        $departmentDB = db('department');
        foreach ($data as $key => $val) {
            // 额外属性信息
            $extrainfo = Memberextrainfo::where('memberId', $val['id'])->find();
            if($extrainfo){
                $val['householdNum'] = $extrainfo['householdNum'];
                $val['personNum'] = $extrainfo['personNum'];
                $val['familyNumber'] = Memberextrainfo::where('householdNum', $extrainfo['householdNum'])->count();
                $val['householderId'] = MemberMdl::where('id', $extrainfo['householderId'])->value('name');
                if($extrainfo['isHouseholder'] == 1){
                    // 是户主
                    $val['relatedHouseholder'] = '户主';
                }else{
                    // 不是户主
                    // 与户主关系
                    $relatedHouseholderArr = get_relatedHouseholder();
                    $val['relatedHouseholder'] = $relatedHouseholderArr[$extrainfo['relatedHouseholder']];
                }
                // 劳动技能
                //$laborSkillsArr = get_laborSkills();
                //$val['laborSkills'] = $laborSkillsArr[$extrainfo['laborSkills']];
                $val['laborSkills'] = $extrainfo['laborSkills'];
                $val['perIncome'] = $extrainfo['perIncome'] / 100;
                // 务工状况
                //$workStatusArr = get_workStatus();
                //$val['workStatus'] = $workStatusArr[$extrainfo['workStatus']];
                $val['workStatus'] = $extrainfo['workStatus'];
                $val['workTime'] = $extrainfo['workTime'];
                // 是否是特殊群体
                switch ($extrainfo['specialGroup']) {
                    case 0: $val['specialGroup'] = '否'; break;
                    case 1: $val['specialGroup'] = '留守儿童'; break;
                    case 2: $val['specialGroup'] = '空巢老人'; break;
                    default : break;
                }
            }else{
                $val['householdNum'] = '';
                $val['personNum'] = '';
                $val['familyNumber'] = 1;
                $val['householderId'] = '';
                $val['relatedHouseholder'] = '';
                $val['laborSkills'] = '';
                $val['perIncome'] = '';
                $val['workStatus'] = '';
                $val['workTime'] = '';
                $val['specialGroup'] = '';
            }
            // 是否是贫困户
            if($val['isPoverty'] == 1){
                $povertyinfo = Povertymember::where('memberId', $val['id'])->find();
                $val['povertydegree'] = $povertyinfo['povertydegree'];
                $val['povertyreason'] = $povertyinfo['povertyreason'];
                $val['outpoverty'] = $povertyinfo['outpoverty'];
                $val['outPovertyYear'] = $povertyinfo['outPovertyYear'];
                $val['isDangerHouse'] = ($povertyinfo['isDangerHouse'] == 1) ? '是' : '否';
                $val['isSeriousIllness'] = ($povertyinfo['isSeriousIllness'] == 1) ? '是' : '否';
            }else{
                $val['povertydegree'] = '';
                $val['povertyreason'] = '';
                $val['outpoverty'] = '';
                $val['outPovertyYear'] = '';
                $val['isDangerHouse'] = '';
                $val['isSeriousIllness'] = '';
            }

            // 所属城市
            $val['cityId'] = $areaDB->where(['id' => $val['cityId']])->value('name');
            // 所属县
            $val['xianId'] = $areaDB->where(['id' => $val['xianId']])->value('name');
            // 所属镇
            $val['townId'] = $areaDB->where(['id' => $val['townId']])->value('name');
            // 所属村
            $val['villageId'] = $villageDB->where(['id' => $val['villageId']])->value('name');
            // 所属机构
            if($val['institutionId']){
                $val['institutionId'] = $institutionDB->where(['id' => $val['institutionId']])->value('name');
            }else{
                $val['institutionId'] = '';
            }
            // 所属部门
            if($val['departmentId']){
                $val['departmentId'] = $departmentDB->where(['id' => $val['departmentId']])->value('name');
            }else{
                $val['departmentId'] = '';
            }
            // 是否为贫困户
            switch ($val['isPoverty']) {
                case 1: $val['isPoverty'] = '是'; break;
                case 2: $val['isPoverty'] = '否'; break;
                default : $val['isPoverty'] = '否'; break;
            }
            // 是否为党员
            switch ($val['isParty']) {
                case 1: $val['isParty'] = '是'; break;
                case 2: $val['isParty'] = '否'; break;
                default : $val['isParty'] = '否'; break;
            }
            // 所属组织
            if($val['organizationId']){
                $val['organizationId'] = $organizationDB->where(['id' => $val['organizationId']])->value('name');
            }else{
                $val['organizationId'] = '';
            }
            // 入党时间
            if($val['partyTime']){
                $val['partyTime'] = date('Y-m-d', $val['partyTime']);
            }else{
                $val['partyTime'] = '';
            }
            // 身份证
            $val['identityNumber'] = (string) $val['identityNumber'];
        }
        $cellName = array(
            'A' => ['id', '编号'],
            'B' => ['cityId', '所属城市'],
            'C' => ['xianId', '所属县'],
            'D' => ['townId', '所属镇'],
            'E' => ['villageId', '所属村'],
            'F' => ['householdNum', '户编号'],
            'G' => ['personNum', '人编号'],
            'H' => ['name', '姓名'],
            'I' => ['avatar', '头像'],
            'J' => ['gender', '性别'],
            'K' => ['birthday', '生日'],
            'L' => ['identityNumber', '身份证号'],
            'M' => ['mobile', '手机号码'],
            'N' => ['familyNumber', '人数'],
            'O' => ['householderId', '户主'],
            'P' => ['relatedHouseholder', '与户主关系'],
            'Q' => ['ethnicId', '名族'],
            'R' => ['education', '文化程度'],
            'S' => ['health', '健康状况'],
            'T' => ['religiousFaith', '宗教信仰'],
            'U' => ['laborSkills', '劳动技能'],
            'V' => ['perIncome', '人均收入(单位：元)'],
            'W' => ['workStatus', '务工状况'],
            'X' => ['workTime', '务工时间(单位：月)'],
            'Y' => ['specialGroup', '是否特殊群体'],
            'Z' => ['address', '详细住址'],
            'AA' => ['villageJob', '村职务'],
            'AB' => ['institutionId', '所属机构'],
            'AC' => ['departmentId', '所属部门'],
            'AD' => ['institutionJob', '机构职务名称'],
            'AE' => ['isPoverty', '是否为贫困户'],
            'AF' => ['povertydegree', '贫困户属性'],
            'AG' => ['povertyreason', '致贫原因'],
            'AH' => ['outpoverty', '脱贫属性'],
            'AI' => ['outPovertyYear', '脱贫年度'],
            'AJ' => ['isDangerHouse', '是否危房户'],
            'AK' => ['isSeriousIllness', '是否参加大病医疗'],
            'AL' => ['isParty', '是否为党员'],
            'AM' => ['organizationId', '所属组织'],
            'AN' => ['job', '职务名称'],
            'AO' => ['partyTime', '入党时间'],
            'AP' => ['createDate', '创建时间'],
            'AQ' => ['updateDate', '更新时间'],
        );

        $filePath = ROOT_PATH . 'public' . DS . 'upload' . DS . 'csv' . DS . 'member' . DS . date('Y-m-d');
        $fileName = $filePath . '/member_' . date('y-m-d-H-i-s');

        $excel = new Excel();
        $excel->exportExcel('村民', $data, $cellName, $filePath, $fileName);

        return [
            'code' => 1,
            'msg' => '导出成功！'
        ];
    }

}